<?php
/**
 * DDCrosstab Class File
 * 
 * @author Joachim Werner <joachim.werner@diggin-data.de>
 * @link http://www.diggin-data.de
 */

/**
 * DDCrosstab creates crosstab for database queries
 *
 * @author  Joachim Werner <joachim.werner@diggin-data.de>
 * @version 0.1
 */
class DDCrosstab extends CWidget
{
    // {{{ Members
    /**
     * If set to true, debug infos are echoed
     * 
     * @var bool
     * @access public
     */
    public $debug = false;
    /**
     * SQL clause for row field
     * 
     * @var string
     * @access public
     */
    public $row;
    /**
     * Label for crosstab row header
     * 
     * @var string
     * @access public
     */
    public $rowLabel = 'Rows';
    /**
     * SQL clause for column field
     * 
     * @var string
     * @access public
     */
    public $column;
    /**
     * Label for crosstab column header
     * 
     * @var string
     * @access public
     */
    public $columnLabel = 'Columns';
    /**
     * Type of cells
     *
     * Either 'count', 'id' or 'card'
     * 
     * @var string
     * @access public
     */
    public $cellsType = 'count';
    /**
     * SQL clause for crosstab cells 
     * 
     * @var mixed
     * @access public
     */
    public $cells;
    /**
     * Label for crosstab cells 
     * 
     * @var mixed
     * @access public
     */
    public $cellsLabel;
    /**
     * Drilldown link for cell values
     *
     * Either 
     *
     * <code>
     * array('controller/action','par1'=>..., 'par2'=>...)
     * </code>
     *
     * or an URL as string
     * 
     * @var mixed
     * @access public
     */
    public $cellsLink = null;
    public $from;
    public $join = null;
    public $leftJoins = null;
    public $rightJoins = null;
    public $where = null;
    public $group = null;
    /**
     * Array of additional HTML options for the textarea control
     * 
     * @var array
     * @access public
     */
    public $htmlOptions=array();
    /**
     * The ID of the crosstab table  
     * 
     * @var mixed
     * @access private
     */
    private $_crosstabId;

    // }}}
    // {{{ run
    /**
     * Runs the widget 
     * 
     * @access public
     * @return void
     */
    public function run()
    {
        $this->registerClientScripts();
        $this->renderContent();
    } // }}} 
    // {{{ registerClientScripts
    /**
     * Registers the clientside widget files (css & js)
     */
    private function registerClientScripts() {
        self::resolveID($this->htmlOptions);
        $this->_crosstabId = $this->htmlOptions['id'];
    } // }}} 
    // {{{ renderContent
    protected function renderContent()
    {
        $crosstab = $this->getCrosstab();
        //return;
        $this->render(
            'crosstab',
            array(
                'crosstab'=>$crosstab,
                'htmlOptions'=>$this->htmlOptions,
                'crosstabId' => $this->_crosstabId,

            )
        );
    } // }}} 
    // {{{ getCrosstab
    /**
     * Returns a crosstab object with settings and data
     * 
     * @access public
     * @return mixed
     */
    public function getCrosstab()
    {
        ob_start();
        $command = Yii::app()->db->createCommand();
        // {{{ Select
        $command->select(array($this->row.' AS row', $this->column.' AS `column`', $this->cells.' AS cell'));
        $command->select = str_replace('``','`',$command->select);
        // }}} 
        // {{{ From
        $command->from($this->from);
        // }}} 
        // {{{ Joins
        if(!is_null($this->join))
            $command = $command->join($this->join);
        // }}} 
        // {{{ Left Joins
        if(!is_null($this->leftJoins)) {
            foreach($this->leftJoins as $leftJoin) {
                switch(count($leftJoin))
                {
                    case 1:
                        $command = $command->leftJoin($leftJoin[0]);
                        break;
                    case 2:
                        $command = $command->leftJoin($leftJoin[0],$leftJoin[1]);
                        break;
                    case 3:
                        $command = $command->leftJoin($leftJoin[0],$leftJoin[1],$leftJoin[2]);
                        break;
                }
            }
        }
        // }}} 
        // {{{ Right Joins
        if(!is_null($this->rightJoins)) {
            foreach($this->rightJoins as $rightJoin) {
                switch(count($rightJoin))
                {
                    case 1:
                        $command = $command->rightJoin($rightJoin[0]);
                        break;
                    case 2:
                        $command = $command->rightJoin($rightJoin[0],$rightJoin[1]);
                        break;
                    case 3:
                        $command = $command->rightJoin($rightJoin[0],$rightJoin[1],$rightJoin[2]);
                        break;
                }
            }
        }
        // }}} 
        // {{{ Where
        if(!is_null($this->where))
            $command = $command->where($this->where);
        // }}} 
        // {{{ Group
        if(in_array($this->cellsType,array('id','card')))
            $command = $command->group(array($this->row,$this->column, $this->cells));
        else
            $command = $command->group(array($this->row,$this->column));
        // }}} 
        // {{{ Order
        if(in_array($this->cellsType,array('id','card')))
            $command->order(array('row', 'column', $this->cells));
        else
            $command->order(array('row', 'column'));
        // }}} 
        // Get rows
        $rows = $command->queryAll();
        // DEBUG 
        echo par($rows,'$rows');

        echo par($command->getPdoStatement());

        // Set up crosstab object
        $result = new StdClass;
        $result->columns = array();
        $result->rows = array();
        $result->labels = new StdClass;
        $result->labels->row = $this->rowLabel;
        $result->labels->column = $this->columnLabel;
        $result->labels->cells = $this->cellsLabel;
        $result->cellsLink = $this->cellsLink;
        foreach($rows as $row) {
            echo par($row,'$row');
            if(!in_array($row['column'],$result->columns))
                $result->columns[] = $row['column'];
            $result->rows[$row['row']][$row['column']][] = $row['cell'];
        }
        // fill up empty cells
        foreach($result->rows as $rowValue=>$cells) {
            // DEBUG 
            echo "<li>rowValue: $rowValue";
            // DEBUG 
            echo par($cells, '$cells');
            foreach(array_values($result->columns) as $columnIndex=>$columnValue) {
                // DEBUG 
                echo "<li>columnIndex: $columnIndex, columnValue: $columnValue";
                if(!array_key_exists($columnValue, $result->rows[$rowValue]))
                    $result->rows[$rowValue][$columnValue] = '';
            }
            ksort($result->rows[$rowValue]);
            // DEBUG 
            echo par($result->rows[$rowValue], '$cells neu');
        }
        echo par($result, '$result');
        $cts = ob_get_contents();
        ob_end_clean();
        if($this->debug)
            echo $cts;
        return $result;
    } // }}} 
    // {{{ resolveID
    /**
     * Generates input name and ID for a model attribute.
     * This method will update the HTML options by setting appropriate 'name' and 'id' attributes.
     * This method may also modify the attribute name if the name
     * contains square brackets (mainly used in tabular input).
     * @param CModel the data model
     * @param string the attribute
     * @param array the HTML options
     */
    public static function resolveID(&$htmlOptions)
    {
            if(!isset($htmlOptions['id'])) {
                $htmlOptions['id']='crosstab-'.str_replace('.','',microtime(true));
            } else {
                $htmlOptions['id'] = 'crosstab-'.str_replace('.','',microtime(true));
            }
    } // }}} 
    // {{{ getIdByName
    /**
     * Generates a valid HTML ID based the name.
     * @return string the ID generated based on name.
     */
    public static function getIdByName($name)
    {
            return str_replace(array('[]', '][', '[', ']'), array('', '_', '_', ''), $name);
    } // }}}
    // {{{ resolveName
    /**
     * Generates input name for a model attribute.
     * Note, the attribute name may be modified after calling this method if the name
     * contains square brackets (mainly used in tabular input) before the real attribute name.
     * @param CModel the data model
     * @param string the attribute
     * @return string the input name
     * @since 1.0.2
     */
    public static function resolveName($model,&$attribute)
    {
        if(($pos=strpos($attribute,'['))!==false)
        {
                if($pos!==0)  // e.g. name[a][b]
                        return get_class($model).'['.substr($attribute,0,$pos).']'.substr($attribute,$pos);
                if(($pos=strrpos($attribute,']'))!==false && $pos!==strlen($attribute)-1)  // e.g. [a][b]name
                {
                        $sub=substr($attribute,0,$pos+1);
                        $attribute=substr($attribute,$pos+1);
                        return get_class($model).$sub.'['.$attribute.']';
                }
                if(preg_match('/\](\w+\[.*)$/',$attribute,$matches))
                {
                        $name=get_class($model).'['.str_replace(']','][',trim(strtr($attribute,array(']['=>']','['=>']')),']')).']';
                        $attribute=$matches[1];
                        return $name;
                }
        }
        else
                return get_class($model).'['.$attribute.']';
    } // }}} 
}

/* vim: set ai sw=4 sts=4 et fdm=marker fdc=4: */ 
